# loading libraries
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import plotly.express as px
from wordcloud import WordCloud

import warnings
warnings.filterwarnings("ignore")

# reading the data
df = pd.read_csv('./data/sf_budget_2022-25_cleaned.csv')
df.shape

df.head()

# overview of the data file
def data_overview(df):
    # Initialize an empty DataFrame
    info_df = pd.DataFrame(index=df.columns)
    
    # Add columns to the info DataFrame
    info_df['DataType'] = df.dtypes
    info_df['Count'] = df.count()
    info_df['Missing Values'] = df.isnull().sum()
    info_df['Unique Values'] = df.nunique()
    
    # Calculate min and max values only for integer columns
    int_columns = df.select_dtypes(include='int').columns
    info_df.loc[int_columns, 'Min Value'] = df[int_columns].min()
    info_df.loc[int_columns, 'Max Value'] = df[int_columns].max()
    
    # Use a lambda function to apply value_counts to each column
#     info_df['Value Counts'] = df.apply(lambda x: x.value_counts().to_dict())
    
    return info_df

data_overview(df)

# Make sure 'budget2022', 'budget2023', 'budget2024' are numerical columns
df_melted = pd.melt(df, id_vars=None, value_vars=['budget_2022_23', 'budget_2023_24', 'budget_2024_25'],
                    var_name='Budget Year', value_name='Budget')

# Plot the box plot using Plotly
fig = px.box(df_melted, x='Budget Year', y='Budget',
             labels={'Budget': 'Budget'},
             title='Box Plot of Budget by Year',
             height=500)
fig.update_layout(showlegend=False)  # No need for legend in this case
fig.show()

# Melt the DataFrame
df_melted = pd.melt(df, id_vars=None, value_vars=['budget_2022_23', 'budget_2023_24', 'budget_2024_25'],
                    var_name='Budget Year', value_name='Budget')

# Calculate whiskers for outlier removal
Q1 = df_melted['Budget'].quantile(0.25)
Q3 = df_melted['Budget'].quantile(0.75)
IQR = Q3 - Q1
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR

# Remove outliers
df_no_outliers = df_melted[(df_melted['Budget'] >= lower_bound) & (df_melted['Budget'] <= upper_bound)]

# Plot the box plot without outliers
fig = px.box(df_no_outliers, x='Budget Year', y='Budget',
             points="all",  # Show all points (not just outliers)
             labels={'Budget': 'Budget'},
             title='Box Plot of Budget by Year (Without Outliers)',
             height=500)
fig.update_layout(showlegend=False)  # No need for legend in this case
fig.show()

# Looking at budget 2022-2023 distribution across service_area
fig = px.bar(df, x='service_area', y='budget_2022_23', color='service_area', title='Budget Distribution by Service',
             labels={'budget_2022_23': 'Budget', 'service_area': 'Service'})

fig.update_layout(xaxis_title='Service', yaxis_title='Budget', barmode='stack')

fig.show()

# Notes and updates - 
# why is there that one negative value? inference?
# Explore if we can add a drop down for the budget columns to easily switch between the years
# too much information. can we make the category names smaller?

df.columns

# Make sure 'budget2022', 'budget2023', 'budget2024' are numerical columns
df['change_22_23_to_23_24'] = df['budget_2023_24'] - df['budget_2022_23']
df['change_23_24_to_24_25'] = df['budget_2024_25'] - df['budget_2023_24']

# Plot the change using Plotly
fig = px.bar(df, x='service_area', y=['change_22_23_to_23_24', 'change_23_24_to_24_25'],
             labels={'value': 'Budget Change'},
             title='Budget Change from 22-23 to 23-24 and from 23-24 to 24-25 by Service',
             height=500)
fig.update_layout(barmode='group')
fig.show()

# this chart needs to be improved as the calculations are per row 
# instead of a groupby.

# top 15 departments (out of 56) by budget in a horizontal bar chart

# calculating total budget at department level
df_grouped = df.groupby('department')['budget_2022_23'].sum().reset_index()
df_grouped = df_grouped.sort_values(by='budget_2022_23', ascending=False).head(15)  # Displaying top 10 categories

fig = px.bar(df_grouped, x='budget_2022_23', y='department', orientation='h',
             title='Top Departments by Budget',
             labels={'budget': 'budget_2022_23', 'department': 'Department'},
             )

fig.update_layout(xaxis_title='Budget', yaxis_title='Department', barmode='stack')

fig.show()

# Notes:
# out of the total 14.1 billion, 3 billion is in public health
# followed by general city responsibility and public utilities

text = ' '.join(df['department'].dropna().astype(str))

# Create and generate a word cloud image
wordcloud = WordCloud(
    width=800,
    height=400,
    background_color='white',
    contour_width=3,
    contour_color='steelblue',
    colormap='viridis',  # You can change the color map
    max_words=200  # Adjust the number of words displayed
).generate(text)

# Save the word cloud as an image with higher DPI
wordcloud.to_file("wordcloud.png")

# Display the interactive version using Plotly
fig = px.imshow(wordcloud.to_array(), binary_string=True, height=400, width=800)
fig.update_layout(coloraxis_showscale=False)
fig.show()

# notes-
# we can explore service, airport, numicipal, transportation separately
# can we make this plot with budget as the metric instead of value counts?

import chardet

with open('./data/addbacksSpreadsheet_cleaned.csv', 'rb') as f:
    result = chardet.detect(f.read())

df2 = pd.read_csv('./data/addbacksSpreadsheet_cleaned.csv', encoding=result['encoding'])

df2 = pd.read_csv('./data/addbacksSpreadsheet_cleaned.csv')